package com.yonyou.common;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;

public class DBUtil
{
  public int getTableRowCount(String tableName)
  {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    int rowCount = -1;
    try {
      String sql = "select * from " + tableName;
      con = getCon();
      pst = con.prepareStatement(sql, 1004, 
        1007);
      rs = pst.executeQuery();
      rs.last();
      rowCount = rs.getRow();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } finally {
      close(con, pst, rs);
    }
    return rowCount;
  }

  public boolean hasElem(String tableName, String keyName, Object key)
  {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql = "select * from " + tableName + " where " + 
      firstUpper(keyName) + "=?";
    try {
      con = getCon();
      pst = con.prepareStatement(sql);
      setObject(pst, 1, key);
      rs = pst.executeQuery();
      if (rs.next())
        return true;
    }
    catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      close(con, pst, rs); } close(con, pst, rs);

    return false;
  }

  public ArrayList<Object> getTableElem(String tableName, String beanName)
  {
    return getElemList(tableName, beanName, 1, -1);
  }

  public ArrayList<Object> getElemList(String tableName, String beanName, int offset, int count)
  {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ArrayList rs_list = new ArrayList();
    try {
      String sql = "select * from " + tableName;
      con = getCon();
      pst = con.prepareStatement(sql, 1004, 
        1007);
      rs = pst.executeQuery();
      String[] paramName = tableColName(rs);
      Class beanClass = Class.forName(beanName);
      if (offset > 1)
        rs.absolute(offset - 1);
      else if ((offset < 0) && (offset != -1))
        return rs_list;
      do {
        Object bean = beanClass.newInstance();
        saveBean(bean, rs, paramName);
        rs_list.add(bean);

        if (!rs.next()) break; 
      }while ((count == -1) || (count-- > 0));
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      e.printStackTrace();
    } catch (InstantiationException e) {
      e.printStackTrace();
    } finally {
      close(con, pst, rs);
    }
    return rs_list;
  }

  public ArrayList<Object> fuzzySearch(String tableName, String beanName, String fuzzyKeyName, String fuzzyKey, Object[][] key, int offset, int count)
  {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    ArrayList rs_list = new ArrayList();
    try {
      String sql = "select * from " + tableName + " where " + 
        firstUpper(fuzzyKeyName) + " like ?";
      for (int i = 0; i < key.length; i++) {
        if (key[i].length == 2) {
          sql = sql + " and " + firstUpper((String)key[i][0]) + "=?";
        }
      }
      con = getCon();
      pst = con.prepareStatement(sql, 1004, 
        1007);
      pst.setObject(1, "%" + fuzzyKey + "%");
      for (int i = 0; i < key.length; i++) {
        if (key[i].length == 2) {
          setObject(pst, i + 2, key[i][1]);
        }
      }
      rs = pst.executeQuery();
      String[] paramName = tableColName(rs);
      Class beanClass = Class.forName(beanName);
      if (offset > 1)
        rs.absolute(offset - 1);
      else if ((offset < 0) && (offset != -1))
        return rs_list;
      do {
        Object bean = beanClass.newInstance();
        saveBean(bean, rs, paramName);
        rs_list.add(bean);

        if (!rs.next()) break; 
      }while ((count == -1) || (count-- > 0));
    }
    catch (SQLException e)
    {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      e.printStackTrace();
    } catch (InstantiationException e) {
      e.printStackTrace();
    } finally {
      close(con, pst, rs);
    }
    return rs_list;
  }

  public Object getElem(String tableName, String keyName, Object key, String beanName)
  {
    Connection con = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    String sql = "select * from " + tableName + " where " + 
      firstUpper(keyName) + "=?";
    String[] paramName = null;
    try {
      con = getCon();
      pst = con.prepareStatement(sql);
      setObject(pst, 1, key);
      rs = pst.executeQuery();
      paramName = tableColName(rs);

      Class beanClass = Class.forName(beanName);
      Object bean = null;
      bean = beanClass.newInstance();
      if (rs.next()) {
        saveBean(bean, rs, paramName);
      }
      Object localObject2 = bean;
      return localObject2;
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (InstantiationException e) {
      e.printStackTrace();
    } catch (IllegalAccessException e) {
      e.printStackTrace();
    } finally {
      close(con, pst, rs);
    }

    return null;
  }

  public int insert(String tableName, Object bean)
  {
    Connection con = null;
    PreparedStatement pst = null;
    StringBuilder sql = new StringBuilder("insert into " + tableName + "(");
    try {
      Object[][] beanObj = bean2Array(bean);
      int n = beanObj.length;
      if (n < 1) {
        return 0;
      }
      for (int i = 0; i < n; i++) {
        if (i != 0)
          sql.append(",");
        sql.append(beanObj[i][0]);
      }
      sql.append(") values(");
      for (int i = 0; i < n; i++) {
        if (i != 0)
          sql.append(",");
        sql.append("?");
      }
      sql.append(")");
      con = getCon();
      pst = con.prepareStatement(sql.toString());
      for (int i = 0; i < n; i++) {
        setObject(pst, i + 1, beanObj[i][1]);
      }
      int i = pst.executeUpdate();
      return i;
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } finally {
      close(con, pst);
    }
    return 0;
  }

  public int updateElem(String tableName, String keyName, Object key, Object bean)
  {
    Connection con = null;
    PreparedStatement pst = null;
    StringBuilder sql = new StringBuilder("update " + tableName + " set ");
    try {
      Object[][] beanObj = bean2Array(bean);
      int n = beanObj.length;
      if (n < 1) {
        return 0;
      }
      for (int i = 0; i < n; i++) {
        if (i != 0)
          sql.append(",");
        sql.append(beanObj[i][0] + "=?");
      }
      sql.append(" where " + firstUpper(keyName) + "=?");
      con = getCon();
      pst = con.prepareStatement(sql.toString());
      int i = 0;
      for (; i < n; i++) {
        setObject(pst, i + 1, beanObj[i][1]);
      }
      setObject(pst, i + 1, key);
      int id = pst.executeUpdate();
      return id;
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } finally {
      close(con, pst);
    }
    return 0;
  }

  public int deleteElem(String tableName, String keyName, Object key)
  {
    Connection con = null;
    PreparedStatement pst = null;
    StringBuilder sql = new StringBuilder("delete from " + tableName);
    try {
      sql.append(" where " + firstUpper(keyName) + "=?");
      con = getCon();
      pst = con.prepareStatement(sql.toString());
      setObject(pst, 1, key);
      int count = pst.executeUpdate();
      int i = count;
      return i;
    } catch (SQLException e) {
      e.printStackTrace();
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (IllegalArgumentException e) {
      e.printStackTrace();
    } finally {
      close(con, pst);
    }
    return 0;
  }

  private void setObject(PreparedStatement pst, int i, Object obj)
    throws SQLException
  {
    if (obj == null) {
      pst.setString(i, null);
    } else if (obj.getClass() == Date.class) {
      long l = ((Date)obj).getTime();
      Timestamp t = new Timestamp(l);
      pst.setTimestamp(i, t);
    } else {
      pst.setObject(i, obj);
    }
  }

  private Object[][] bean2Array(Object bean)
  {
    Class beanClass = bean.getClass();
    Field[] field = beanClass.getDeclaredFields();
    Method[] method = beanClass.getDeclaredMethods();
    ArrayList list = new ArrayList();
    for (int i = 0; i < field.length; i++) {
      for (int j = 0; j < method.length; j++) {
        String methodName = method[j].getName();
        if ((!isGetMethod(methodName)) || 
          (!methodName.contains(firstUpper(field[i].getName())))) continue;
        Object o = null;
        try {
          o = method[j].invoke(bean, new Object[0]);
        } catch (IllegalArgumentException e) {
          e.printStackTrace();
        } catch (IllegalAccessException e) {
          e.printStackTrace();
        } catch (InvocationTargetException e) {
          e.printStackTrace();
        }
        if (o != null) {
          list.add(field[i].getName());
          list.add(o);
        }
      }
    }

    int m = list.size();
    if ((m < 1) || (m % 2 != 0)) {
      return new Object[0][0];
    }
    Object[][] bean_obj = new Object[m / 2][2];
    Iterator iter = list.iterator();
    int i = 0;
    while (iter.hasNext()) {
      bean_obj[i][0] = iter.next();
      bean_obj[i][1] = iter.next();
      i++;
    }
    return bean_obj;
  }

  private String[] tableColName(ResultSet rs) throws SQLException
  {
    ResultSetMetaData rsmd = rs.getMetaData();
    int n = rsmd.getColumnCount();
    String[] name = new String[n];
    for (int i = 0; i < n; i++) {
      name[i] = rsmd.getColumnName(i + 1);
    }
    return name;
  }

  private void saveBean(Object bean, ResultSet rs, String[] paramName)
    throws SQLException
  {
    Method[] method = bean.getClass().getDeclaredMethods();
    for (int i = 0; i < paramName.length; i++)
      for (int j = 0; j < method.length; j++) {
        String methodName = method[j].getName();
        if ((!methodName.endsWith(paramName[i])) || 
          (!isSetMethod(methodName))) continue;
        Object param = rs.getObject(paramName[i]);
        if (param == null)
          continue;
        if (param.getClass() == Timestamp.class) {
          long l = ((Timestamp)param).getTime();
          param = new Date(l);
        }
        try {
          method[j].invoke(bean, new Object[] { param });
        } catch (IllegalArgumentException e) {
          e.printStackTrace();
        } catch (IllegalAccessException e) {
          e.printStackTrace();
        } catch (InvocationTargetException e) {
          e.printStackTrace();
        }
      }
  }

  private boolean isGetMethod(String methodName)
  {
    if (methodName == null)
      return false;
    return (methodName.startsWith("is")) || (methodName.startsWith("get"));
  }

  private boolean isSetMethod(String methodName)
  {
    if (methodName == null)
      return false;
    return methodName.startsWith("set");
  }

  private String firstUpper(String s)
  {
    return s.substring(0, 1).toUpperCase() + s.substring(1);
  }

  private Connection getCon() throws ClassNotFoundException, SQLException
  {
    DB.getInstance();
    return DB.getConnection();
  }

  private void close(Connection con, PreparedStatement pst, ResultSet rs)
  {
    try {
      if (rs != null)
        rs.close();
      if (pst != null)
        pst.close();
      if (con != null)
        DB.release(con);
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  private void close(Connection con, PreparedStatement pst)
  {
    try
    {
      if (pst != null)
        pst.close();
      if (con != null)
        con.close();
    }
    catch (SQLException e) {
      e.printStackTrace();
    }
  }
}